Passing comma-delimited values into SAS macros and macro functions

12

Problem solving: thinking inside the box

Have you ever tried to pass comma-delimited values to SAS macro or to a SAS macro function? How can SAS distinguish commas separating parameters or arguments from commas separating parts of the values?

Passing comma-delimited value as an argument to a SAS macro function

Let’s say you want to extract the first word from the following string of characters (these words represent column names in the SASHELP.CARS data table):

make, model, type, origin

If you run the following code:

%let firstvar = %scan(make, model, type, origin, 1);

you get is the following ERROR in your SAS log:

ERROR: Macro function %SCAN has too many arguments.

That is because %scan macro function sees and treats those make, model, type and origin as arguments since commas between them are interpreted as argument separators.

Even if you “hide” your comma-delimited value within a macro variable, it still won’t do any good since the macro variable gets resolved during macro compilation before being passed on to a macro or macro function for execution.

%let mylist = make, model, type, origin;
%let firstvar = %scan(&mylist, 1);

You will still get the same ERROR:

ERROR: Macro function %SCAN has too many arguments.

Passing comma-delimited value as a parameter to a SAS macro

Try submitting the following code that passes your macro variable value to a SAS macro as a parameter:

%let mylist = make, model, type, origin;
%macro subset(dsname=, varlist=);
   proc sql;
      select &varlist
      from &dsname;
   quit;
%mend subset;
%subset(dsname=SASHELP.CARS, varlist=&mylist)

You will get another version of the SAS log ERROR:

ERROR: All positional parameters must precede keyword parameters.
NOTE: Line generated by the macro variable "MYLIST".
1                 type, origin
                  ----
                  180
ERROR 180-322: Statement is not valid or it is used out of proper order.

In this case, macro %subset gets as confused as the %scan function above because your macro variable will get resolved during macro compilation, and SAS macro processor will see the macro invocation as:

%subset(dsname=SASHELP.CARS, varlist=make, model, type, origin)

treating each comma as a parameter separator.

All this confusion happens because SAS functions’ arguments and SAS macros’ parameters use commas as their separators, while resolved macro variables introduce their own values’ comma delimiters into the functions/macros constructs’ picture, thus wreaking havoc on your SAS program.

It’s time for a vacation

But don’t panic! To fight that chaos, you need to take a vacation. Not a stay-home, do-nothing vacation, but some serious vacation, with faraway destination and travel arrangements. While real vacation is preferable, an imaginary one would do it too. I mean to start fighting the mess with comma-separated values, pick your destination, book your hotel and flight, and start packing your stuff.

Do you have a “vacation items list”? In my family, we have an individual vacation list for every family member. How many items do you usually take with you? Ten, twenty, a hundred?

Regardless, you don’t show up at the airport checkpoint with a pile of your vacation items. That would’ve been too messy. I don’t think you would be even allowed boarding with an unpacked heap of your stuff. You come to an airport neatly rolling a single item that is called a suitcase. Well, I suppose that some of you may have two of them, but I can’t imagine more than that.

You only started your fantasy vacation, you haven’t even checked in to your flight, but you have already have a solution in your sight, a perfect combine-and-conquer solution for passing comma-delimited values. Even if you have not yet realized that it’s in your plain view.

Thinking inside the box

Forget about “thinking outside the box” metaphor. You can’t solve all your problems with a single strategy. Sometimes, you need to turn your thinking on its head to solve, or even to see the problem.

As for your airport check-in, instead of thinking outside the box, you thought “inside the box” and brought your many items “boxed” as a single item – a suitcase. A container, in a broader sense.

That is exactly how we are going to approach our comma-delimited lists problem. We are going to check them in to a macro or a macro function as a single, boxed item. Just like this:
Passing a comma-separated value to SAS macro or SAS macro function
Or like this:
passing SAS macro variable with comma-separated value to SAS macro or SAS macro function

Not surprisingly, SAS macro language provides a variety of these wonder boxes for many special occasions collectively known as macro quoting functions. Personally, I would prefer calling them “macro masking functions,” as they have nothing to do with “quoting” per se and have everything to do with masking various characters during macro compilation or macro processing. But that is what “macro quoting” means – masking, boxing, - similar to “quoting” a character string to make it a single entity.

Different macro quoting functions mask different special characters (+ - , / ; = etc.) and mnemonics (AND OR GT EQ etc.) so that the macro facility interprets them as text instead of as language symbols.

Here are all 7 SAS macro quoting functions, two of which work at macro compilation - %STR() and %NRSTR(), while other 5 work at macro execution - %QUOTE() and %NRQUOTE(), %BQUOTE() and %NRBQUOTE(), and %SUPERQ().

You may look up what symbols they mask and the timing they apply (macro compilation vs. macro execution) in this macro quoting functions summary. You may also want to look at the following cheat sheet: Deciding When to Use a Macro Quoting Function and Which Function to Use.

As general rule of thumb, use macro quoting functions at compilation time when you mask text constants - (make, model, type, origin); use macro quoting functions at execution time when you mask macro or macro variable references containing & or % - (&mylist).

NOTE: There are many other SAS macro functions that besides their main role also perform macro quoting, e.g. %QSCAN(), %QSUBSTR() and others; they all start with %Q.

Masking commas within a comma-delimited value passed as an argument or a parameter

It turns out that to mask (or to “box”) comma-separated values in a macro function or a SAS macro, any macro quoting function will work. In this case I would suggest using the simplest (and shortest) %STR(). %STR() applies during macro compilation and serves as a perfect “box” for our comma-delimited values to hide (mask) commas to receiving macro function or a macro does not confuse them with its own commas separating arguments / parameters.

With it we can re-write our above examples as:

%let firstvar = %scan(%str(make, model, type, origin), 1);
%put &=firstvar;

SAS log will produce exactly what we expected:

FIRSTVAR=make

Similarly, we can call the above SAS macro as:

%subset(dsname=SASHELP.CARS, varlist=%str(make, model, type, origin) )

It will run without ERRORs and produce a print of the SASHELP.CARS data table with 4 columns specified by the varlist parameter value:

SAS output table as a result of macro run

Masking commas within a macro variable value passed as an argument or parameter

When you assign a comma-delimited list as a value to a macro variable, we want to mask commas within the resolved value during execution. Any of the execution time macro quoting functions will mask comma.

Again, in case of multiple possibilities I would use the shortest one - %QUOTE().

With it we can re-write our above examples as:

%let mylist = make, model, type, origin;
 
%let firstvar = %scan(%quote(&mylist), 1);
 
%subset(dsname=SASHELP.CARS, varlist=%quote(&mylist))

But just keep in mind that the remaining 4 execution time macro quoting functions - %NRQUOTE(), %BQUOTE(), %NRBQUOTE() and %SUPERQ() - will work too.

NOTE: The syntax of the %SUPERQ() function is quite different from the rest of the pack. The %SUPERQ() macro function takes as its argument either a macro variable name without an ampersand or a macro text expression that yields a macro variable name without an ampersand.

Get it going

I realize that macro quoting is not a trivial matter. That is why I attempted to explain its concept on a very simple yet powerful use case. Hope you will expand on this to empower your SAS coding skills.

Share

About Author

Leonid Batkhan

Leonid Batkhan is a long-time SAS consultant and blogger. Currently, he is a Lead Applications Developer at F.N.B. Corporation. He holds a Ph.D. in Computer Science and Automatic Control Systems and has been a SAS user for more than 25 years. From 1995 to 2021 he worked as a Data Management and Business Intelligence consultant at SAS Institute. During his career, Leonid has successfully implemented dozens of SAS applications and projects in various industries. All posts by Leonid Batkhan >>>

12 Comments

  1. RATNAKARAM SOWMYA on

    Hi, in macros if we mention &sysday we will get Tuesday automatically but if I need some other days like Friday or Monday. How the program should be written?

    • Leonid Batkhan

      &sysday automatic macro variable contains the day of the week on which a SAS session started. If you started your SAS session on Tuesday and keep it open for several days, then no matter when you run your code &sysday will always produce Tuesday. If you want to get the current day of week when your program runs you need to rely on the date() function. For example, the following code produces the day of week of the date of that code execution:

      %put %sysfunc(date(),downame9.);

      Function date() returns current date, and format downame9. converts that date into a word representing day of week.

  2. AMRENDRA SINGH on

    So, when the delimiter is a comma we can use any of the above functions.
    Is it right ?
    Thanks for such brilliant article. Really loved the way you explained the entire thing.

    • Leonid Batkhan

      Hi Amrendra,
      Thank you for your comment. To your question, for the comma-delimited values one can use ANY OF THE EXECUTION TIME macro quoting functions - %quote(), %nrquote(), %bquote(), %nrbquote() and %superq(). That is because we want to mask commas within the resolved value during execution. The other two macro functions that work at macro compilation - %str() and %nstr() - won't work.

  3. Bartosz Jabłoński on

    Hi Leonid,

    @macroQuoting - I'm always recommending Susan O'Conor's article "Secrets of Macro Quoting Functions - How and Why" which is one of my favorite SAS articles explaning macroquoting.

    @commasInArgument - why not to try PARMBUFF option in the %macro definition?

    all the best
    Bart

    • Leonid Batkhan

      Thank you, Bart, for reading and adding these resources.
      Indeed, it's a great paper by Susan O'Conor on macro quoting, it can be downloaded here.

      Regarding PARMBUFF option, sure one can use it paired with the SYSPBUFF automatic macro variable. Also, you can use a data table or an external file as a data store for multiple values, in this case you can just pass a single parameter pointing to a location of such a table/file/datastore.

      • Bartosz Jabłoński on

        One more funny "trick" came into my mind:

        %macro test(x, y);
        %put &=x &=y;
        %mend test;

        %test((a,b,c), 17) /* wrap them in (...) */

        but it need some additional work on x though 😉

        all the best
        Bart

          • Bartosz Jabłoński on

            Hi Leonid,

            Sorry for late reply, but I forgot about this thread 🙂

            I think I would remove brackets with %qsubstr(...) and %sysfunc(reverse(...)), like:

            %macro test(x, y);
              %put &=x &=y;
              %let z = %qsysfunc(reverse(%qsubstr(%qsysfunc(reverse(%qsubstr(&x.,2))),2)));
              %put &=z;
            %mend test;
            
            %test((a, b, c), 17)
            

            But if &x. will be submitted to %qscan(...) function we don't have to remove brackets, since %qscan(...) would ignore "blank" argument before the first and after the last delimiter, like:
            %macro test2(x, y);
              %put &=x &=y;
              %do i = 1 %to %qsysfunc(countw(&x.,%str((,))));
                %let a = %unquote(%qscan(&x., &i., %str((,))));
                %put &=i *&=a*;
              %end;
            %mend test2;
            
            %test2((a, b, c), 17)
            

            All the best
            Bart

            • Leonid Batkhan

              Thank you, Bart, for following it through. You offered a great solution for removing the brackets as a problem on its own. However, for the purpose of passing comma-separated value as a macro parameter, I would rather not create that bracket problem in the first place (even though it's very tempting and beautiful), and just use either %str(a, b, c) if a, b and c are resolved values, or %quote(&varlist) where %let varlist = a, b, c;

              But as long as you come up with that "bracket way" of passing in parameters I do admire your persistence and appreciate your input and the solution.

              Best regards,
              Leonid

  4. Leonid,

    Excellent metaphors; excellent techniques!

    I always learn something new from your blog posts; which shows that you can, indeed, teach an "old" dog new tricks!

    --Michael

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top